Before I learned about the use of an ORM (Object-Relational Mapping), I handled all interactions with the database through either stored procedures or building a query and setting up parameters. It was not the most exciting part of development. When I was introduced to NHibernate, an ORM for .net based on Hibernate, an ORM for java, I jumped right in and started bringing it into my projects.

There are hundreds of different ORMs. The goal of most ORMs is to abstract interactions with the database, support multiple databases, and handle mapping. Some implementations also extend the base database functionality by adding caching, lazy loading, and bulk operations, to name a few. The most common denominator of these ORMs is that they generate SQL statements for you. Some do this through their own query language which could be string-based (HQL), or through methods that build the request internally.

In the past few years, I have completely moved away from using ORMs in my personal projects and when I have to work with ORMs, I am very cautious. I know SQL well enough to know what good SQL looks like. I’m also always having to work extra hard to ensure that the generated SQL performs well. ORMs can generate beautiful SQL, but it can also generate statements that are overly verbose and slow.

I’m not here to say that ORMs are evil and you must always stay away from them. I’m here to show the issues that using an ORM can bring to light. I want to start with the abstraction most ORMs provide. Abstraction is an amazing thing. ORMs use abstraction to provide a common API that can speak to different database flavors. The abstraction is what excited me at first. I thought being able to swap out the database with minimal changes to the code was amazing. I’ve used the ability to swap out a database, my use-case being that I wanted the ability to do unit testing against an in-memory database. It was pretty great, at least that was my opinion at the time.

Abstraction is not a bad thing. It can speed up development by dealing with most of the inner workings of database interactions. Where abstraction becomes a problem is that it is generating SQL for you. It is true that this opens the door for developers that are not comfortable with SQL, it forces developers to learn the ORM. Each ORM has a learning curve and each has its own API to interact with the database. This can cause developers to focus on the ORM, not the SQL it is generating. To be good at an ORM, I believe you not only need to understand the SQL it is producing, but you also must know how to use the API to get it to build the SQL you need it to produce. Most ORMs do allow you to write your own SQL, but that is typically not recommended, especially for the more opinionated ORM frameworks. There have been too many times that I have struggled to get ORMs to build SQL the way I need it. I typically find myself falling back to straight SQL for complex queries. ORMs are usually great at creating simple queries but when you need something more advanced, it can be tough to get what you need out of it. End of the day, my issue with the abstraction that ORMs provide is that they hide SQL. This can become a crutch for some people as they put off learning SQL and instead learn the ORM. If you need to switch to another language or system that uses a different ORM, you now need to learn the new ORM.

In my experience, to properly use most ORMs, you have to adapt your system to use it, which can become very invasive, requiring you to have to follow specific patterns to even use it. Some require you to use their own query language, others require you to build your objects with metadata. which is used to handle mappings. These are not bad things, but the more code you have to tailor to the ORM, the harder it is to replace it if you need to. Of course, you can abstract an ORM by isolating it and putting as much as you can behind interfaces but in my opinion, external dependencies such as a database should almost always be abstracted, especially if you want to bring in unit testing that does not actually hit the database.

I am a fan of almost anything that simplifies and speeds up development. Yes, ORMs do exactly that, but sometimes the way they do it can cause pain in the long run. I believe that anyone who uses an ORM should understand SQL well enough to understand the SQL that is generated. I also want to caution when using more advanced ORM features such as lazy-loading. These features can provide a lot of value but they can also cause unintended side-effects. I’ll never forget a bug that took too much time to track down. I was handling an object returned from NHibernate that had quite a few other table dependencies that were set up for lazy-loading. You could force the loading of the data at the time of the queries execution or you can let the ORM query the extra data when you need to use it. An example could be a User table. A user might have one or more roles. You would get a user object from the ORM and when you access that objects roles property, the ORM will execute an additional query to load that uses roles. This can be an awesome feature that can provide huge performance boosts. That said, it can also cause issues if your lazy-loading loads other lazy-loaded data. My bug was just that. Because of all of the lazy-loading, the state of my object and its properties were hard to debug. My issue ended up being that the state of the object didn’t always reflect the state of the database, especially as other processes were interacting with the same underlying data, changing the state of my dependencies as I accessed the lazy-loaded properties.

I’m taking my stab at creating an ORM for Typescript that is extremely lite and using its helpers is completely optional. I would say I’m about 60% done and I hope to finish it by October. I’m naming it nORM which stands for no ORM. when I’m ready, I’ll publish it to GitHub and create an NPM package.